package com.hdu.dwh.pojos.SQLBuilder;

import java.util.LinkedHashSet;
import java.util.Set;
import java.util.stream.Collectors;

import com.hdu.dwh.pojos.DimensionFilter;
import com.hdu.dwh.pojos.DimensionPivot;
import com.hdu.dwh.pojos.MetricFilter;
import com.hdu.dwh.pojos.cube_pojos.Filter;

import lombok.Data;

@Data
@lombok.EqualsAndHashCode(callSuper = false)
public class SQLSelectBuilder extends SQLBulder {
    private LinkedHashSet<String> selectFields;
    private Set<DimensionPivot> pivots;
    private Set<Join> joins;
    private Set<Filter> whereFilters;
    private Set<String> groupByFields;
    private Set<MetricFilter> havingFields;
    private Integer limit;
    private Integer offset;

    public SQLSelectBuilder select(String... fields) {
        this.selectFields = new LinkedHashSet<String>(Set.of(fields));
        return this;
    }

    public SQLSelectBuilder select(LinkedHashSet<String> fields) {
        this.selectFields = fields;
        return this;
    }

    public SQLSelectBuilder from(String schema, String table) {
        this.databaseName = schema;
        this.tableName = table;
        return this;
    }

    public SQLSelectBuilder join(String joinType, String fullTableName, String onCondition) {
        if (this.joins == null) {
            this.joins = new java.util.HashSet<>();
        }
        this.joins.add(new Join(joinType, fullTableName, onCondition));
        return this;
    }

    public SQLSelectBuilder where(Filter... filters) {
        for (Filter filter : filters) {
            if (this.whereFilters == null) {
                this.whereFilters = new java.util.HashSet<>();
            }
            this.whereFilters.add(filter);
        }
        return this;
    }

    public SQLSelectBuilder where(Set<Filter> filters) {
        for (Filter filter : filters) {
            if (this.whereFilters == null) {
                this.whereFilters = new java.util.HashSet<>();
            }
            this.whereFilters.add(filter);
        }
        return this;
    }

    public SQLSelectBuilder groupBy(String... fields) {
        this.groupByFields = Set.of(fields);
        return this;
    }

    public SQLSelectBuilder groupBy(Set<String> fields) {
        this.groupByFields = fields;
        return this;
    }

    public SQLSelectBuilder having(Set<MetricFilter> fields) {
        this.havingFields = fields;
        return this;
    }

    public SQLSelectBuilder limit(Integer limit) {
        this.limit = limit;
        return this;
    }

    public SQLSelectBuilder offset(Integer offset) {
        this.offset = offset;
        return this;
    }

    public String build() {
        StringBuilder sql = new StringBuilder();
        // Build SELECT clause
        sql.append("SELECT ").append(String.join(", ", selectFields)).append(" ");
        // Build FROM clause
        sql.append("FROM ").append(databaseName + "." + tableName).append(" ");
        // Build JOIN clauses
        if (joins != null) {
            for (Join join : joins) {
                sql.append(join.getJoinType()).append(" JOIN ").append(join.getTable())
                        .append(" ON ").append(join.getOnCondition()).append(" ");
            }
        }
        // Build WHERE clause
        if (whereFilters != null && !whereFilters.isEmpty()) {
            sql.append("WHERE ");
            String whereClause = whereFilters.stream()
                    .map(f -> f.getField() + " " + f.getOperator() + " " + f.getValue())
                    .collect(Collectors.joining(" AND "));
            sql.append(whereClause).append(" ");
        }
        // Build GROUP BY clause
        if (groupByFields != null && !groupByFields.isEmpty()) {
            sql.append("GROUP BY ").append(String.join(", ", groupByFields)).append(" ");
        }
        // Build HAVING clause
        if (havingFields != null && !havingFields.isEmpty()) {
            sql.append("HAVING ");
            String havingClause = havingFields.stream()
                    .map(f -> String.format("%s(%s) %s %s", f.getAggFuncName(), f.getField(), f.getOperator(),
                            f.getValue()))
                    .collect(Collectors.joining(" AND "));
            sql.append(havingClause).append(" ");
        }
        // Build OFFSET clause
        if (offset != null) {
            sql.append("OFFSET ").append(offset).append(" ");
        }
        // Build LIMIT clause
        if (limit != null) {
            sql.append("LIMIT ").append(limit).append(" ");
        }

        return sql.toString().trim();
    }

    // SQLBuilder sqlBuilder = new SQLBuilder();
    // SQLBuilder.select("field1", "field2")
    // .from("table1")
    // .join("left", "table2", "table1.id = table2.id")
    // .where(new Filter("field1, "=", "value1"), new Filter("field2", ">",
    // "value2"))
    // .groupBy("field1")
    // .orderBy("field2", "DESC")
    // .limit(10)
    // .offset(20);
}

@Data
class Join {
    private String joinType; // INNER, LEFT, RIGHT, FULL
    private String table;
    private String onCondition;

    public Join(String joinType, String table, String onCondition) {
        this.joinType = joinType;
        this.table = table;
        this.onCondition = onCondition;
    }
}